unit ControleAcesso;

interface

uses Menus, DmGeral, SysUtils, DB, DBClient, DBLocals, Dialogs, Classes;

function CA_UsuarioAdmin(const usuario : string) : boolean;
function CA_RetornarCodigoMenu(const menu : string; DataSet : TCustomClientDataSet = nil) : integer;
function CA_VerificarAcesso(const menu : integer; DataSet : TCustomClientDataSet) : boolean;
function CA_RetiraCaracterAtalho(const texto : String) : String;
procedure CA_VerificaAcessoSubMenu(const menu : TMenuItem; DataSet : TCustomClientDataSet);
function CA_RetornaProximoCodigoMenu : integer;
procedure CA_VerificaOpcoesMenu(const usuario : string; const menu : TMenuItem; DataSet : TCustomClientDataSet; var SequenciaNovos : integer);
procedure CA_AtualizarMenus(const usuario : string;mmPrincipal : TMainMenu); // onCreate
procedure CA_ExibirMenus(const usuario:string;menu : TMainMenu);// onShow
procedure CA_LimparGrupos(const usuario : string);
function CA_CarregarGrupos(const usuario : string = '') : TDataSet;
procedure CA_Associar(const usuario : string; const grupo: array of integer);
procedure CA_ControleAcesso(const usuario : string; const menu : integer;out PodeConsultar,
  PodeInserir, PodeEditar, PodeExcluir : boolean);
// Menus Favoritos
function CA_MenuFavoritos(const usuario : string; MenuPrincipal : TMainMenu) : TStrings;
function CA_ControleFuncao(const usuario: string; const codmenu: integer): TStrings;

implementation

uses SqlExpr, Forms;

function CA_UsuarioAdmin(const usuario : string) : boolean;
begin
  with DtmGeral.SQLQ_Auxiliar do begin
    Close;
    SQL.Text := 'SELECT USU_SUPERVISOR FROM USUARIOS WHERE USU_CODIGO = ' + QuotedStr(Trim(usuario));
    Open;
    if not IsEmpty then
      Result := Fields[0].AsString = 'S'
    else
      Result := false;
    Close;
  end;
end;

function CA_RetornarCodigoMenu(const menu : string; DataSet : TCustomClientDataSet) : integer;
begin
  if DataSet = nil then begin
    with DtmGeral.SQLQ_Auxiliar do begin
      Close;
      SQL.Text := 'SELECT MEN_CODIGO FROM MENUS WHERE MEN_APLICACAO = ' + QuotedStr(Application.Title)
       + ' AND MEN_NOME = ' + QuotedStr(Trim(menu));
      Open;
      if not IsEmpty then
        Result := Fields[0].AsInteger
      else
        Result := -1;
      Close;
    end;
  end
  else begin
    if DtmGeral.CDS_MENU.Active = false then
      DtmGeral.CDS_MENU.Open;
    if DtmGeral.CDS_MENU.Locate('MEN_NOME',menu,[]) then
      Result := DtmGeral.CDS_MENU.FieldByName('MEN_CODIGO').AsInteger;
    DtmGeral.CDS_MENU.Close;
  end;
end;

function CA_VerificarAcesso(const menu : integer; DataSet : TCustomClientDataSet) : boolean;
begin
  with DataSet as TCustomClientDataSet do begin
    if (FindKey([menu])) or (menu = -1) then
      Result := True
    else
      Result := False;
  end;
end;

function CA_RetiraCaracterAtalho(const texto : String) : String;
begin
  Result := StringReplace(texto,'&','',[rfReplaceAll]);
end;

procedure CA_VerificaAcessoSubMenu(const menu : TMenuItem; DataSet : TCustomClientDataSet);
var
  i : Integer;
begin
  if not CA_VerificarAcesso(CA_RetornarCodigoMenu(CA_RetiraCaracterAtalho(menu.Name),DtmGeral.CDS_MENU),DataSet) then
    menu.Visible := False;
  for i := 0 to Pred(menu.Count) do
    CA_VerificaAcessoSubMenu(menu.Items[i],DataSet);
end;

function CA_RetornaProximoCodigoMenu : integer;
begin
  with DtmGeral.SQLQ_Auxiliar  do
  begin
    Close;
    SQL.Text := 'SELECT MAX(MEN_CODIGO) AS MAIOR FROM MENUS';
    Open;
    Result := Fields[0].AsInteger + 1;
    Close;
  end;
end;

procedure CA_VerificaOpcoesMenu(const usuario: string; const menu : TMenuItem; DataSet : TCustomClientDataSet; var SequenciaNovos : integer);
var
  i, codMenu, codMenuPai : Integer;
begin
  with DataSet do
  begin
    if not Locate('MEN_NOME',menu.Name,[]) then
    begin
      if Assigned(menu.Parent) and (menu.Parent.Name <> '') then
      begin
        Locate('MEN_NOME',menu.Parent.Name,[]);
        codMenuPai := FieldByName('MEN_CODIGO').AsInteger;
        Append;
        FieldByName('MEN_CODIGO').AsInteger := SequenciaNovos;
        FieldByName('MEN_NOME').AsString := Trim(CA_RetiraCaracterAtalho(menu.Name));
        FieldByName('MEN_CODIGOPAI').AsInteger := codMenuPai;
        FieldByName('MEN_EXISTE').AsString := 'S';
        FieldByName('MEN_APLICACAO').AsString := Application.Title;
        FieldByName('MEN_CAPTION').AsString := Trim(CA_RetiraCaracterAtalho(menu.Caption));
      end
      else
      begin
        Append;
        FieldByName('MEN_CODIGO').AsInteger := SequenciaNovos;
        FieldByName('MEN_NOME').AsString := Trim(CA_RetiraCaracterAtalho(menu.Name));
        FieldByName('MEN_EXISTE').AsString := 'S';
        FieldByName('MEN_APLICACAO').AsString := Application.Title;
        FieldByName('MEN_CAPTION').AsString := Trim(CA_RetiraCaracterAtalho(menu.Caption));
      end;
      Inc(SequenciaNovos);
      Post;
    end
    else
    begin
      Edit;
      FieldByName('MEN_NOME').AsString := Trim(CA_RetiraCaracterAtalho(menu.Name));
      FieldByName('MEN_EXISTE').AsString := 'S';
      FieldByName('MEN_APLICACAO').AsString := Application.Title;
      FieldByName('MEN_CAPTION').AsString := Trim(CA_RetiraCaracterAtalho(menu.Caption));
      Post;
    end;
  end;
  for i := 0 to Pred(menu.Count) do
    CA_verificaOpcoesMenu(usuario, menu.Items[i],DataSet,SequenciaNovos);
end;

function CA_MenuFavoritos(const usuario : string; MenuPrincipal : TMainMenu) : TStrings;
var
  MenuFavoritos , ItemFavorito: TMenuItem;
  Query : TSQLQuery;
  i : integer;
begin
  MenuFavoritos := TMenuItem.Create(nil);
  MenuFavoritos.Caption := 'Meus Favoritos';
  Query := TSQLQuery.Create(nil);
  with Query do begin
    SQLConnection := DtmGeral.ConexaoBanco;
    SQL.Add('SELECT  MEN.MEN_CAPTION, MEN.MEN_CODIGO, UF.USF_POSICAO');
    SQL.Add('FROM MENUS MEN');
    SQL.Add('INNER JOIN');
    SQL.Add('USUARIO_FAVORITOS UF');
    SQL.Add('ON UF.USF_MENIDF = MEN.MEN_CODIGO');
    SQL.Add('WHERE UF.USF_USUIDF = :USF_USUIDF');
    SQL.Add('ORDER BY UF.USF_POSICAO');
    ParamByName('USD_USUIDF').AsString := usuario;
    Open;
    i := 0;
    Result := TStringList.Create;
    while not Eof do begin
      ItemFavorito := TMenuItem.Create(nil);
      ItemFavorito.Caption := FieldByName('MEN_CAPTION').AsString;
      ItemFavorito.Name := 'Favorito' + IntToStr(i);
      Inc(i);
      Result.Add(FieldByName('MEN_CODIGO').AsString);
      MenuFavoritos.Add(ItemFavorito);
      Next;
    end;
    Query.Free;
    MenuPrincipal.Items.Add(MenuFavoritos);
    MenuFavoritos.Visible := true;
    ItemFavorito := nil;
  end;
end;

procedure CA_AtualizarMenus(const usuario : string;mmPrincipal : TMainMenu); // onCreate
var
  i , sequencia_novo_menu , index: Integer;
  Favs : TStrings;
  Favoritos : TMenuItem;
begin
  with DtmGeral.CDS_MENU do begin
    Open;
    sequencia_novo_menu := CA_RetornaProximoCodigoMenu;
    for i := 0 to Pred(mmPrincipal.Items.Count) do
      CA_VerificaOpcoesMenu(usuario, mmPrincipal.Items[i],
                        DtmGeral.CDS_MENU, sequencia_novo_menu);
    First;
    while not eof do begin
      if FieldByName('MEN_EXISTE').AsString = 'N' then
        Delete;
      Next;
    end;
    ApplyUpdates(0);
    DtmGeral.ZConexaoBanco.Disconnect;
    DtmGeral.ZConexaoBanco.Connect;
  end;
end;

procedure CA_ExibirMenus(const usuario:string; menu : TMainMenu);// onShow
var
  i : Integer;
begin
  if not CA_UsuarioAdmin(usuario) then begin
    DtmGeral.CDS_MENU_ACESSO.Params[0].AsString := usuario;
    DtmGeral.CDS_MENU_ACESSO.Open;
    for i := 0 to Pred(menu.Items.Count) do
      CA_verificaAcessoSubMenu(menu.Items[i],DtmGeral.CDS_MENU_ACESSO);
    DtmGeral.CDS_MENU_ACESSO.Close;
  end;
end;

procedure CA_LimparGrupos(const usuario : string);
begin
  with DtmGeral.SQLQ_Auxiliar do begin
    if Active then
      Close;
    SQL.Text := 'DELETE FROM GRUPOS_USUARIOS WHERE USU_CODIGO = ' + QuotedStr(Usuario);
    ExecSQL(True);
  end;
end;

function CA_CarregarGrupos(const usuario : string) : TDataSet;
var
  DataSet : TSQLClientDataSet;
begin
  DataSet := TSQLClientDataSet.Create(nil);
  DataSet.DBConnection := DtmGeral.ConexaoBanco;
  with DataSet do begin
    if usuario = '' then
      CommandText := 'SELECT GRU_CODIGO, GRU_DESCRICAO FROM GRUPOS ORDER BY GRU_DESCRICAO'
    else begin
      CommandText := 'SELECT GRU_CODIGO FROM GRUPOS_USUARIOS WHERE USU_CODIGO = '
        + QuotedStr(usuario);
    end;
    Open;
  end;
  Result := DataSet;
end;

procedure CA_Associar(const usuario : string; const grupo: array of integer);
var
  i : integer;
  DataSet : TSQLClientDataSet;
begin
  DataSet := TSQLClientDataSet.Create(nil);
  with DataSet do begin
    DBConnection := DtmGeral.ConexaoBanco;
    CommandText := 'SELECT GRU_CODIGO, USU_CODIGO FROM GRUPOS_USUARIOS WHERE USU_CODIGO = ' + QuotedStr(usuario);
    Open;
    for i := 0 to Length(grupo) - 1 do begin
      Append;
      FieldByName('GRU_CODIGO').AsInteger := grupo[i];
      FieldByName('USU_CODIGO').AsString  := usuario;
      Post;
    end;
    ApplyUpdates(0);
  end;
  DataSet.close;
  DataSet.Free;
end;

procedure CA_ControleAcesso(const usuario : string;const menu : integer; out PodeConsultar,
  PodeInserir, PodeEditar, PodeExcluir : boolean);
var
  SP_CONTROLE_ACESSO : TSQLStoredProc;
begin
  SP_CONTROLE_ACESSO := TSQLStoredProc.Create(nil);
  with SP_CONTROLE_ACESSO do begin
    SQLConnection   := DtmGeral.ConexaoBanco;
    StoredProcName  := 'PROC_PERMISSOES_TELAS';
    ParamByName('MEN_CODIGO').AsInteger := menu;
    ParamByName('USU_CODIGO').AsString  := usuario;
    ExecProc;
    PodeConsultar := ParamByName('CONSULTAR').AsString = 'S';
    PodeInserir   := ParamByName('INSERIR').AsString = 'S';
    PodeEditar    := ParamByName('ALTERAR').AsString = 'S';
    PodeExcluir   := ParamByName('EXCLUIR').AsString = 'S';
    Free;
  end;
end;

function CA_ControleFuncao(const usuario: string; const codmenu: integer): TStrings;
var
  Funcoes_NPermitidas: TStringList;
  funcao: string;
begin
  Funcoes_NPermitidas := TStringList.Create;
  with DtMGeral.SQLC_Consulta do begin
    Close;
    CommandText := 'SELECT US.PE_FUNCAO FROM' +
                   ' (SELECT * FROM PERMISSOES_ESPECIAIS '+
                   ' WHERE PE_USUARIOS = '+QuotedStr(usuario) +
                   ' AND PE_MENU = '+QuotedStr(IntToStr(codmenu)) +// ')'+
                   ' AND PE_PERMITE='+QuotedStr('N') + ') US' +
                   ' UNION'+
                   ' SELECT PE.PE_FUNCAO FROM' +
                   ' (SELECT * FROM PERMISSOES_ESPECIAIS) PE'+
                   ' INNER JOIN GRUPOS_USUARIOS GU'+
                   ' ON (GU.GRU_CODIGO = PE.PE_GRUPOS)'+
                   ' WHERE PE_MENU='+QuotedStr(IntToStr(codmenu)) +
                   ' AND PE_PERMITE='+QuotedStr('N');
    Open;
    First;
    while not(Eof) do begin
      funcao := UpperCase(FieldByName('PE_FUNCAO').AsString);
      Funcoes_NPermitidas.Append(Funcao);
      Next;
    end;
    Result := Funcoes_NPermitidas;
  end;
end;

end.
